{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import sys\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签\n",
    "plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "te = pd.read_excel(\"text.xlsx\", index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>feature_name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>-5</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              min  max\n",
       "feature_name          \n",
       "A               0    5\n",
       "B               1   10\n",
       "C              -5  100"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "te"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "tr = pd.read_excel(\"test.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A    B      C\n",
       "0  1.0  0.0 -100.0\n",
       "1  6.0  9.0  200.0\n",
       "2  4.0  5.0    6.0"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tr.astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 6\n",
      "数据上限超出范围！\n",
      "0 9\n",
      "数据下限超出范围！\n",
      "-100 200\n",
      "数据上下限均超出范围！\n"
     ]
    }
   ],
   "source": [
    "def Judge_Up_Low_Limit(feature_name, min_value, max_value):\n",
    "    if te.loc[feature_name]['min'] <= min_value and te.loc[feature_name]['max'] >= max_value:\n",
    "        return 1\n",
    "    elif te.loc[feature_name]['min'] > min_value and te.loc[feature_name]['max'] < max_value:\n",
    "        return 4\n",
    "    elif te.loc[feature_name]['min'] > min_value:        \n",
    "        return 2\n",
    "    elif te.loc[feature_name]['max'] < max_value:        \n",
    "        return 3\n",
    "\n",
    "for i in range(len(tr.columns)):\n",
    "    mean_value = round(tr[tr.columns[i]].mean(), 6)\n",
    "    std_value = round(tr[tr.columns[i]].std(), 6)\n",
    "    min_value = round(tr[tr.columns[i]].min(), 6)\n",
    "    quantile_14 = round(tr[tr.columns[i]].quantile(q=0.25), 6)\n",
    "    quantile_24 = round(tr[tr.columns[i]].quantile(q=0.5), 6)\n",
    "    quantile_34 = round(tr[tr.columns[i]].quantile(q=0.75), 6)\n",
    "    max_value = round(tr[tr.columns[i]].max(), 6)\n",
    "    D_value = max_value - min_value\n",
    "    print(min_value, max_value)\n",
    "    judge_limit_res = Judge_Up_Low_Limit(tr.columns[i], min_value, max_value)\n",
    "    if judge_limit_res == 1:\n",
    "        print(\"数据范围正常！\")\n",
    "    elif judge_limit_res == 2:\n",
    "        print(\"数据下限超出范围！\")\n",
    "        tr.loc[ tr[tr.columns[i]] < te.loc[ tr.columns[i] ]['min'], tr.columns[i] ] = te.loc[ tr.columns[i] ]['min']\n",
    "    elif judge_limit_res == 3:\n",
    "        print(\"数据上限超出范围！\")\n",
    "        tr.loc[tr[tr.columns[i]] > te.loc[ tr.columns[i] ]['max'], tr.columns[i] ] = te.loc[ tr.columns[i] ]['max']\n",
    "    elif judge_limit_res == 4:\n",
    "        print(\"数据上下限均超出范围！\")\n",
    "        tr.loc[ tr[tr.columns[i]] < te.loc[ tr.columns[i] ]['min'], tr.columns[i] ] = te.loc[ tr.columns[i] ]['min']\n",
    "        tr.loc[ tr[tr.columns[i]] > te.loc[ tr.columns[i] ]['max'], tr.columns[i] ] = te.loc[ tr.columns[i] ]['max']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   A  B    C\n",
       "0  1  1   -5\n",
       "1  5  9  100\n",
       "2  4  5    6"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
